package com.bahiaapk.dao;

import java.text.ParseException;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.List;
import java.util.Locale;
import org.json.JSONArray;
import org.json.JSONException;
import org.json.JSONObject;
import android.content.Context;
import android.database.Cursor;
import android.database.SQLException;
import android.database.sqlite.SQLiteDatabase;
import android.util.Log;
import com.bahiaapk.dto.News;
import com.bahiaapk.util.BahiaUtil;

public class NewsDAO {

	private Context context;
	private SQLiteDatabase database;
	private DBHelper dbHelper;

	public NewsDAO(Context context) {
		this.context = context;
	}

	public NewsDAO openWritable() throws SQLException {
		dbHelper = new DBHelper(context);
		database = dbHelper.getWritableDatabase();
		return this;
	}
	public NewsDAO openReadable() throws SQLException {
		dbHelper = new DBHelper(context);
		database = dbHelper.getReadableDatabase();
		return this;
	}
	

	public void close() {
		dbHelper.close();
	}
	
	public List<News> findAll () {
		this.openReadable();

		List<News> result = new ArrayList<News>();		
		Cursor cursor;
			 
		try{
			cursor = database.rawQuery("SELECT ID," +
									   "title, " +
									   "urlImage, " +
									   "date " +
									   "FROM tbl_news " +
									   "WHERE active = 1 " +
									   "order by date desc limit 0,60", null);
			cursor.moveToFirst();
			
			if (!cursor.isAfterLast()){
				do{
					result.add(this.bindSQLite(cursor));
				}while (cursor.moveToNext());
			}
			
			cursor.close();
		}catch (SQLException e){
			Log.e(BahiaUtil.TAG, e.toString());
		}
		
		this.close();
		
		return result;
	}
	
	public List<News> findByRanking() {
		this.openReadable();

		List<News> result = new ArrayList<News>();		
		Cursor cursor;
			 
		try{
			cursor = database.rawQuery("SELECT ID," +
					   "title, " +
					   "urlImage, " +
					   "date " +
					   "FROM tbl_news " +
					   "WHERE active = 1 " +
					   "order by ranking, date desc limit 0,15", null);
			
			cursor.moveToFirst();
			
			if (!cursor.isAfterLast()){
				do{
					result.add(this.bindSQLite(cursor));
				}while (cursor.moveToNext());
			}
			
			cursor.close();
		}catch (SQLException e){
			Log.e(BahiaUtil.TAG, e.toString());
		}
		
		this.close();
		
		return result;
	}
	
	public void insert(JSONArray jsonArray) throws JSONException, ParseException {
		this.openWritable();
		Cursor cursor = null;
		try{
			database.beginTransaction();
			for (int i = 0; i < jsonArray.length(); i++) {
				JSONObject json = jsonArray.getJSONObject(i);
				boolean find = false;
	 		
				cursor = database.rawQuery("SELECT ID " +
										   "FROM tbl_news " +
										   "WHERE ID = "+json.getLong("ID"), null);
				cursor.moveToFirst();
				
				if (!cursor.isAfterLast()){
					find =true;
				}
				cursor.close();

				
				if(!find){
					cursor = database.rawQuery(" INSERT INTO tbl_news (ID,  title, urlImage, date, content, shortContent, active, urlWebsite, ranking)  " +
							"VALUES (" + json.getLong("ID")            +",  " +
									"'"+json.getString("title")        +"', "+
									"'"+json.getString("urlImage")     +"', " +
									"'"+json.getString("date") 	       +"', "+
									"'"+ json.getString("content") 	   +"', " +
									"'"+ json.getString("shortContent")+"', " +
									"" +json.getInt("active")          +", " +
									"'"+json.getString("urlWebsite")   +"', " +
									""+json.getInt("ranking")+");", null);
					
					cursor.moveToFirst();
					cursor.close();
				}else{
					cursor = database.rawQuery(" UPDATE tbl_news SET "+
							"title ='"		  +json.getString("title")		 +"', "+
							"urlImage ='" 	  +json.getString("urlImage")    +"', "+
							"content ='" 	  +json.getString("content")	 +"', "+
							"date ='" 		  +json.getString("date")		 +"', "+
							"shortContent ='" +json.getString("shortContent")+"', "+
							"urlWebsite ='"   +json.getString("urlWebsite")	 +"', "+
							"ranking ="  	  +json.getInt("ranking")		 +"', "+
							"active ="  	  +json.getInt("active")+
							" WHERE ID = "+json.getLong("ID")+";", null);
						
					cursor.moveToFirst();		
					cursor.close();
					
				}
			}
		}catch (SQLException e) {
			Log.e(BahiaUtil.TAG, e.toString());
		}finally {
			
			database.setTransactionSuccessful();
			database.endTransaction();
			
		}
		
		this.close();
		
	}
	
	
	public News findByPrimaryKey(News newssDetail) {
		this.openReadable();

		News result = null;		
		Cursor cursor;
			 
		try{
			cursor = database.rawQuery("SELECT ID," +
					   "title, " +
					   "urlImage, " +
					   "date, " +
					   "content, " +
					   "shortContent, " +
					   "active," +
					   "urlWebsite, "+
					   "sent  " +
					   "FROM tbl_news " +
					   "WHERE ID = '"+newssDetail.getId()+"'", null);
			cursor.moveToFirst();
			
			if (!cursor.isAfterLast())
				result = this.bindSQLite(cursor);
			
			cursor.close();
		}catch (SQLException e){
			Log.e(BahiaUtil.TAG, e.toString());
		}
		
		this.close();
		return result;
	}
	
	public void removeInactive() {
		this.openWritable();
		Cursor cursor = null;
		try{
			cursor = database.rawQuery("DELETE FROM tbl_news WHERE active = 0", null);
			cursor.moveToFirst();
			cursor.close();
		}catch (SQLException e) {
			Log.e(BahiaUtil.TAG, e.toString());
		}
		this.close();
		
	}

	public void updateSent(News news) {
		this.openWritable();
		Cursor cursor;
		try{
			cursor = database.rawQuery(" UPDATE tbl_news SET sent = 1  WHERE ID = "+news.getId()+";", null);
				
			
			cursor.moveToFirst();
			cursor.close();
		}catch (SQLException e) {
			Log.e(BahiaUtil.TAG, e.toString());
		}
		
		this.close();
		
		
	}
	
	
	private News bindSQLite(Cursor cursor){
		News news  = new News();
		SimpleDateFormat dateFormat =  new SimpleDateFormat("EEE MMM dd HH:mm:ss zzz yyyy", Locale.US);
		for (int i = 0; i < cursor.getColumnCount(); i++){
		
				if("ID".equals(cursor.getColumnName(i)))
					news.setId(cursor.getLong(i));
				else if("title".equals(cursor.getColumnName(i))) 
					news.setTitle(cursor.getString(i));
				else if("urlImage".equals(cursor.getColumnName(i))) 
					news.setUrlImage(cursor.getString(i));
				else if("date".equals(cursor.getColumnName(i)))
					try {	
						news.setDate(dateFormat.parse(cursor.getString(i)));
					} catch (ParseException e) {
						Log.e(BahiaUtil.TAG, e.getMessage());
					}
				else if("content".equals(cursor.getColumnName(i))) 
					news.setContent(cursor.getString(i));
				else if("shortContent".equals(cursor.getColumnName(i)))
					news.setShortContent(cursor.getString(i));
				else if("active".equals(cursor.getColumnName(i)))
					news.setActive(cursor.getInt(i));
				else if("urlWebsite".equals(cursor.getColumnName(i))) 
					news.setUrlWebsite(cursor.getString(i));
				else if("sent".equals(cursor.getColumnName(i)))
					news.setRanking(cursor.getInt(i));
		}
		return news;
	}
}
